package com.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class Mysql extends SQLiteOpenHelper {

    // 数据库名称和版本号
    private static final String DATABASE_NAME = "News";
    private static final int DATABASE_VERSION = 1;

    private static final String TABLE_NAME = "Users";

    // 构造函数
    public Mysql(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // 当数据库首次创建时调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        //用户表
        String CREATE_USERS_TABLE = "CREATE TABLE users (" +
                "userid INTEGER PRIMARY KEY AUTOINCREMENT," +//用户id 自增主键
                "password TEXT NOT NULL," +//密码
                "name TEXT," +//用户名
                "avatar_url TEXT DEFAULT NULL ," +//头像url
                "created_at TIMESTAMP DEFAULT NULL )";
        db.execSQL(CREATE_USERS_TABLE);
        //新闻表
        String CREATE_NEWS_TABLE = "CREATE TABLE news (" +
                "newsid INTEGER PRIMARY KEY AUTOINCREMENT," +//主键 新闻id
                "title TEXT NOT NULL," +//标题
                "content TEXT NOT NULL," +//正文
                "category TEXT," +//分类
                "author TEXT," +//作者
                "publishedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +//发布时间
                "imageUrl TEXT" +//图片url
                ")";
        db.execSQL(CREATE_NEWS_TABLE);
        //评论表
        String CREATE_COMMENTS_TABLE = "CREATE TABLE IF NOT EXISTS comments (" +
                "commentid INTEGER PRIMARY KEY AUTOINCREMENT," + // 评论id 自增主键
                "newsid INTEGER NOT NULL," + // 外键，关联新闻ID
                "userid INTEGER NOT NULL," + // 外键，关联用户ID
                "username TEXT,"+//用户名
                "content TEXT NOT NULL," + // 评论内容
                "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP," + // 创建时间，使用默认当前时间
                "parentid INTEGER," + // 可选，关联回复的评论ID
                "FOREIGN KEY (newsid) REFERENCES news(news_id)," + // 假设news表有一个news_id字段作为主键
                "FOREIGN KEY (userid) REFERENCES users(userid)" + // 关联users表的userid字段
                ")";
        db.execSQL(CREATE_COMMENTS_TABLE);
        //点赞表
        String CREATE_LIKES_TABLE = "CREATE TABLE IF NOT EXISTS likes (" +
                "likeid INTEGER PRIMARY KEY AUTOINCREMENT," +
                "userid INTEGER NOT NULL," +
                "newsid INTEGER," + // 可为空，因为用户可能点赞的是评论
                "commentid INTEGER," + // 可为空，因为用户可能点赞的是新闻
                "created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP," +
                "is_active BOOLEAN DEFAULT TRUE," +//表示是否点赞过
                "FOREIGN KEY (userid) REFERENCES users(userid)" + // 假设users表存在且userid是主键
                // 注意：没有直接的约束来确保newsid和commentid只有一个被设置
                ")";

// 执行SQL语句来创建表
        db.execSQL(CREATE_LIKES_TABLE);
    }

    // 当数据库版本发生变化时调用
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // 这里可以处理版本升级的逻辑，比如修改表结构
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
        onCreate(db);
    }

    // 其他数据库操作方法...
}